package com.xinan.hygl;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.kit.StringKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.xinan.xs.HyglCz;

import java.sql.Timestamp;
import java.util.List;
import java.sql.Date;

@TableBind(tableName = "hygl")
public class Hygl extends Model<Hygl>{
    public static final Hygl dao = new Hygl();

    //columns START
    private String ID = "id";  //
    private String CARD = "card";  //卡号
    private String YHM = "yhm";  //姓名
    private String XB = "xb"; //性别
    private String CSRQ = "csrq"; //出生日期
    private String BKRQ = "bkrq";  //办卡日期
    private String GDDH = "gddh";  //固定电话
    private String WXH = "wxh";  //微信号
    private String YSJE = "ysje";  //原始金额
    private String YE = "ye";  //余额
    private String CX = "cx";  //车型
    private String CK = "ck";  //车款
    private String CPH = "cph";  //车牌号
    private String CPH2 = "cph2"; //车牌号2
    private String BXRQ = "bxrq";  //保险日期
    private String DH = "dh";  //保险日期
    private String BZ = "bz"; //备注
    private String NJRQ="njrq";
    public void setId(int value) {
        set(ID, value);
    }

	public int getId() {
		return get(ID);
	}
    public void setCard(String value) {
        set(CARD, value);
    }

	public String getCard() {
		return get(CARD);
	}
    public void setYhm(String value) {
        set(YHM, value);
    }

	public String getYhm() {
		return get(YHM);
	}
    public void setBkrq(Date value) {
        set(BKRQ, value);
    }

	public Date getBkrq() {
		return get(BKRQ);
	}
    public void setGddh(String value) {
        set(GDDH, value);
    }

	public String getGddh() {
		return get(GDDH);
	}
    public void setWxh(String value) {
        set(WXH, value);
    }

	public String getWxh() {
		return get(WXH);
	}
    public void setYsje(String value) {
        set(YSJE, value);
    }

	public String getYsje() {
		return get(YSJE);
	}
    public void setYe(String value) {
        set(YE, value);
    }

	public String getYe() {
		return get(YE);
	}
    public void setCx(String value) {
        set(CX, value);
    }

	public String getCx() {
		return get(CX);
	}
    public void setCk(String value) {
        set(CK, value);
    }

	public String getCk() {
		return get(CK);
	}
    public void setCph(String value) {
        set(CPH, value);
    }

	public String getCph() {
		return get(CPH);
	}
    public void setBxrq(Date value) {
        set(BXRQ, value);
    }

	public Date getBxrq() {
		return get(BXRQ);
	}
	
	public String getDH() {
		return DH;
	}

	public void setDH(String dH) {
		DH = dH;
	}
	
	public String getNJRQ() {
		return NJRQ;
	}

	public void setNJRQ(String nJRQ) {
		NJRQ = nJRQ;
	}
	
	public String getXB() {
		return get(XB);
	}

	public void setXB(String xB) {
		set(XB,xB);
	}

	public Date getCSRQ() {
		return get(CSRQ);
	}

	public void setCSRQ(Date cSRQ) {
		set(CSRQ,cSRQ);
	}

	public String getCPH2() {
		return get(CPH2);
	}

	public void setCPH2(String cPH2) {
		set(CPH2,cPH2);
	}

	public String getBZ() {
		return get(BZ);
	}

	public void setBZ(String bZ) {
		set(BZ,bZ);
	}
	
	
	
	//columns END

    

	

	public List<Hygl> findAll() {
        return find("select * from hygl");
    }
    /**
     * @param   where
     * @return  Record
     */
    public List<Record> findAlls(String where) {
        String sqlExp = " from hygl s";
        if(StringKit.notBlank(where)){
                sqlExp += " where " + where;
        }
        String order =" order  by ye";
        return Db.find( "select *,(select name from codes where code=hykfl) as hykflmc,(select cnname from auth_user where username=s.bkry) as bkrymc," +
        		"ifnull( CAST(ysje   as  DECIMAL(8,1)) ,0) as  ysjes," +
        		" ifnull((select   sum(CAST(czje   as  DECIMAL(8,1)))  from  hygl_cz  a where a.card=s.card  ) ,0) as czje," +
        		"ifnull(  (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as vipcard, " +
        		" ifnull( (select   sum(CAST(moneys   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ),0)  as moneys ," +
        		"ifnull( (select   sum(CAST(xfjf   as  DECIMAL(8,1)))  from  jfjl_hy  a where a.card=s.card  ),0)  as xfjfs  " +sqlExp+order);
    }

    public Page<Hygl> page(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl s";
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        return paginate(pageNumber, pageSize, "select *,(select cnname from auth_user where username=bkry) as bkrymc," +
        		"(select name from codes where code=hykfl) as hykfwmc," +
        		"ifnull( CAST(ysje   as  DECIMAL(8,1)) ,0) as  ysjes," +
        		"ifnull( (select   sum(CAST(czje   as  DECIMAL(8,1)))  from  hygl_cz  a where a.card=s.card  ) ,0) as czje," +
        		" ifnull( (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as vipcard,  " +
        		" ifnull( (select   sum(CAST(moneys   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as moneys ," +
        		"ifnull( (select   sum(CAST(xfjf   as  DECIMAL(8,1)))  from  jfjl_hy  a where a.card=s.card  ),0)  as xfjfs", sqlExp);
    }

    public Page<Hygl> pagetj(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl s";
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        String order=" order by ye";
        return paginate(pageNumber, pageSize, "select *,(select cnname from auth_user where username=bkry) as bkrymc," +
        		"ifnull( CAST(ysje   as  DECIMAL(8,1)) ,0) as  ysjes," +
        		"ifnull( (select   sum(CAST(czje   as  DECIMAL(8,1)))  from  hygl_cz  a where a.card=s.card  ) ,0) as czje," +
        		" ifnull( (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as vipcard,  " +
        		" ifnull( (select   sum(CAST(moneys   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as moneys," +
        		"ifnull( (select   sum(CAST(xfjf   as  DECIMAL(8,1)))  from  jfjl_hy  a where a.card=s.card  ),0)  as xfjfs ", sqlExp+order);
    }
    /*统计办卡金额*/
    public Page<Hygl> pagetj2(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl s";
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        String order=" order by ye";
        return paginate(pageNumber, pageSize, "select *,(select cnname from auth_user where username=bkry) as bkrymc," +
        		"ifnull( CAST(ysje   as  DECIMAL(8,1)) ,0) as  ysjes," +
        		"ifnull( (select   sum(CAST(czje   as  DECIMAL(8,1)))  from  hygl_cz  a where a.card=s.card  ) ,0) as czje," +
        		" ifnull( (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as vipcard,  " +
        		" ifnull( (select   sum(CAST(moneys   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as moneys," +
        		"ifnull( (select   sum(CAST(xfjf   as  DECIMAL(8,1)))  from  jfjl_hy  a where a.card=s.card  ),0)  as xfjfs ", sqlExp+order);
    }

    public List<Record> findUnPayHY(String where){
    	String sqlExp = " from hygl s,hygl_xf s2";
    	String order =" order  by s.card";
        if(StringKit.notBlank(where)){
                sqlExp += " where " + where;
        }
    	return Db.find( "select s.*,(select name from codes where code=hykfl) as hykflmc,(select cnname from auth_user where username=s.bkry) as bkrymc," +
        		"ifnull( CAST(s.ysje   as  DECIMAL(8,1)) ,0) as  ysjes," +
        		" ifnull((select   sum(CAST(czje   as  DECIMAL(8,1)))  from  hygl_cz  a where a.card=s.card  ) ,0) as czje," +
        		"ifnull(  (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ) ,0) as vipcard, " +
        		" ifnull( (select   sum(CAST(moneys   as  DECIMAL(8,1)))  from  hygl_jz  a where a.card=s.card  ),0)  as moneys ," +
        		"ifnull( (select   sum(CAST(xfjf   as  DECIMAL(8,1)))  from  jfjl_hy  a where a.card=s.card  ),0)  as xfjfs  " +sqlExp+order);
    }
    
    /*开卡统计*/
    public Page<Hygl> pagekktj(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl";
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        return paginate(pageNumber, pageSize, "select *  ", sqlExp);
    }
    
}

